﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Windows.Forms;

namespace Factory
{
    class ClienteDAO
    {
        public static IList<Cliente> obtemListaClientes()
        {
            try
            {
                DAOBase m_cnn = new DAOBase();

                #region Query
                string query =
                    @"SELECT ID, Nome, Documento, LimiteAprovado, FlagClienteEspecial,FlagJuridica
                FROM Cliente";
                #endregion

                SqlCommand cmd = new SqlCommand(query, m_cnn.CreateConnection());

                SqlDataReader dr = cmd.ExecuteReader();

                IList<Cliente> clientes = ReaderToObjects(dr);

                return clientes;
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
                return null;
            }
        }

        public static IList<Cliente> ReaderToObjects(IDataReader reader)
        {
            if (reader.IsClosed)
            {
                throw new ArgumentException("O DataReader fornecido já estava fechado.");
            }

            int Id = reader.GetOrdinal("ID");
            int nome = reader.GetOrdinal("Nome");
            int cpf = reader.GetOrdinal("Documento");
            int limite = reader.GetOrdinal("LimiteAprovado");
            int flagAdmin = reader.GetOrdinal("FlagClienteEspecial");
            int flagJuridica = reader.GetOrdinal("FlagJuridica");
            int limiteAtual = reader.GetOrdinal("LimiteAtual");

            IList<Cliente> result = new List<Cliente>();

            while (reader.Read())
            {
                int flag = reader.GetInt32(flagAdmin);
                int flag2 = reader.GetInt32(flagJuridica);
                bool ehAdmin = flag == 1 ? true : false;
                bool ehJuridica = flag2 == 1 ? true : false;
                Cliente c = new Cliente(
                    reader.GetInt32(Id),
                    reader.GetString(cpf),
                    reader.GetDouble(limite),
                    reader.GetString(nome),
                    ehAdmin,
                    ehJuridica,
                    reader.GetDouble(limiteAtual)
                    );
                result.Add(c);
            }
            return result;
        }

        public int CadastrarCliente(Cliente cliente)
        {
            int resultado = 0;

            try
            {

                DAOBase m_cnn = new DAOBase();

                #region query
                string query = @"INSERT INTO Cliente
                                              VALUES
                                               (@nome,@documento,@limite,@especial,@juridica,@limiteAtual)";

                #endregion

                SqlCommand cmd = new SqlCommand(query, m_cnn.CreateConnection());

                //Parametros de Usuario
                cmd.Parameters.Add(new SqlParameter("nome", cliente.nome));
                cmd.Parameters.Add(new SqlParameter("documento", cliente.cpf));
                cmd.Parameters.Add(new SqlParameter("limite", cliente.limiteCredito));
                cmd.Parameters.Add(new SqlParameter("limiteAtual", cliente.limiteCredito));
                cmd.Parameters.Add(new SqlParameter("especial", cliente.especial));
                cmd.Parameters.Add(new SqlParameter("juridica", cliente.juridica));


                resultado = cmd.ExecuteNonQuery();

                m_cnn.DestroyConnection();

            }

            catch (Exception ex)
            {
                //writer.WriteLine("ApplicationException Message: {0}", ex.Message);
            }

            return resultado;
        }

        public IList<Cliente> ObterClientes()
        {
            try
            {
                DAOBase m_cnn = new DAOBase();

                #region Query
                string query =
                    @"SELECT ID, Nome, Documento, LimiteAprovado, FlagClienteEspecial,FlagJuridica,LimiteAtual
                FROM Cliente";
                #endregion

                SqlCommand cmd = new SqlCommand(query, m_cnn.CreateConnection());

                SqlDataReader dr = cmd.ExecuteReader();

                IList<Cliente> clientes = ReaderToObjects(dr);

                return clientes;
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
                return null;
            }
        }

        public int atualizarCliente(Cliente cliente)
        {
            int juridica = cliente.juridica == true ? 1 : 0;
            int especial = cliente.especial == true ? 1 : 0;

            int resultado = 0;

            try
            {
                DAOBase m_cnn = new DAOBase();

                #region query
                string query = @"UPDATE Cliente
                                   SET Nome =@nome
                                      ,Documento = @documento
                                      ,LimiteAprovado = @limite
                                      ,FlagClienteEspecial = @especial
                                      ,FlagJuridica = @juridica
                                      ,LimiteAtual = @limiteAtual
                                 WHERE ID=@ID";

                #endregion

                SqlCommand cmd = new SqlCommand(query, m_cnn.CreateConnection());

                //Parametros de Usuario
                cmd.Parameters.Add(new SqlParameter("ID", cliente.id));
                cmd.Parameters.Add(new SqlParameter("nome", cliente.nome));
                cmd.Parameters.Add(new SqlParameter("documento", cliente.cpf));
                cmd.Parameters.Add(new SqlParameter("limite", cliente.limiteCredito));
                cmd.Parameters.Add(new SqlParameter("limiteAtual", cliente.limiteAtual));
                cmd.Parameters.Add(new SqlParameter("especial", especial));
                cmd.Parameters.Add(new SqlParameter("juridica", juridica));


                resultado = cmd.ExecuteNonQuery();

                m_cnn.DestroyConnection();

            }

            catch (Exception ex)
            {
                //writer.WriteLine("ApplicationException Message: {0}", ex.Message);
            }

            return resultado;
        }

        public Cliente ObterClientesPorId(int id)
        {
            try
            {
                DAOBase m_cnn = new DAOBase();

                #region Query
                string query =
                    @"SELECT ID, Nome, Documento, LimiteAprovado, FlagClienteEspecial,FlagJuridica
                FROM Cliente WHERE ID=@id ";
                #endregion

                SqlCommand cmd = new SqlCommand(query, m_cnn.CreateConnection());
                cmd.Parameters.Add(new SqlParameter("id", id));
                SqlDataReader dr = cmd.ExecuteReader();

                IList<Cliente> clientes = ReaderToObjects(dr);

                return clientes.FirstOrDefault();
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
                return null;
            }
        }

        public int ExcluirCliente(int idCliente)
        {
            int resultado = 0;

            try
            {
                DAOBase m_cnn = new DAOBase();

                #region query
                string query = @"DELETE FROM CLIENTE
                                 WHERE ID=@ID";

                #endregion

                SqlCommand cmd = new SqlCommand(query, m_cnn.CreateConnection());
                cmd.Parameters.Add(new SqlParameter("ID", idCliente));

                resultado = cmd.ExecuteNonQuery();

                m_cnn.DestroyConnection();

            }

            catch (Exception ex)
            {

            }

            return resultado;
        }

        public Cliente ObterClientesPorCpf(string cpf)
        {
            try
            {
                DAOBase m_cnn = new DAOBase();

                #region Query
                string query =
                    @"SELECT ID, Nome, Documento, LimiteAprovado, FlagClienteEspecial,FlagJuridica,LimiteAtual
                FROM Cliente WHERE Documento=@Documento ";
                #endregion

                SqlCommand cmd = new SqlCommand(query, m_cnn.CreateConnection());
                cmd.Parameters.Add(new SqlParameter("Documento", cpf));
                SqlDataReader dr = cmd.ExecuteReader();

                IList<Cliente> clientes = ReaderToObjects(dr);

                return clientes.FirstOrDefault();
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
                return null;
            }
        }

        public int atualizarLimiteCreditoCliente(double limite,string cpf)
        {
            int resultado = 0;

            try
            {
                DAOBase m_cnn = new DAOBase();

                #region query
                string query = @"UPDATE Cliente
                                   SET LimiteAtual = @limite
                                      
                                 WHERE Documento=@Cpf";

                #endregion

                SqlCommand cmd = new SqlCommand(query, m_cnn.CreateConnection());

                //Parametros de Usuario
                cmd.Parameters.Add(new SqlParameter("limite", limite));
                cmd.Parameters.Add(new SqlParameter("Cpf", cpf));
              


                resultado = cmd.ExecuteNonQuery();

                m_cnn.DestroyConnection();

            }

            catch (Exception ex)
            {
                //writer.WriteLine("ApplicationException Message: {0}", ex.Message);
            }

            return resultado;
        }

     
    }
}
